package Herramientas;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PasarelaDatos {
//	PARAMETROS PARA LA CONEXION A LA BASE DE DATOS

    private Connection conex;
    private Statement estSQL;
    PreparedStatement preparedStatement = null;
    private String loginDB = "root";
    private String passwordDB = "adminsys";
    private String dataBase = "jdbc:mysql://localhost:3306/erp_sistema";
    private String databaseName = "erp_sistema";
    private String driver = "com.mysql.jdbc.Driver";

    /**
     * ALAN GONZALEZ METODO QUE EJECUTA CONSULTAS
     *
     * @param expSQL
     * @return
     */
    public ResultSet executeQuery(String expSQL) {
        ResultSet rs = null;
        try {
            estSQL = conex.createStatement();// QUITAR ESTA LINEA CUANDO LOS QUERYS ANIDADOS YA ESTEN CORREGIDOS
            rs = estSQL.executeQuery(expSQL);
            return rs;
        } catch (SQLException e) {
            System.out.println("Error SQL :" + e.getMessage());
            System.out.println("##### Query Error: #####" + expSQL);
        } //fin try Statement
        return rs;
    }

    public ResultSet executeQueryPrepared(String expSQL) {
        ResultSet rs = null;
        try {
            preparedStatement = (PreparedStatement) conex.prepareStatement(expSQL);
            rs = preparedStatement.executeQuery();
        } catch (SQLException e1) {
            System.out.println("Error SQL :" + e1.getMessage());
            e1.printStackTrace();
            System.out.println("##### Query Error: #####" + expSQL);
        }
        return rs;
    }

    /**
     * ALAN GONZALEZ METODO PARA LA INSERCCION DE REGISTROS
     *
     * @param expSQL
     * @return
     */
    public int execute(String expSQL) {
        int resultado = -1;
        try {
//			estSQL = conex.createStatement();
            estSQL.execute(expSQL);
            resultado = estSQL.getUpdateCount();
        } catch (SQLException e) {
            System.out.println("SQLException in execute method:" + e.getMessage());
            System.out.println("##### Query Error: #####" + expSQL);
        }
        return resultado;
    }

    /*
     * SINCRONIZA VARIOS INSER U UPDATE
     */
    public int executeSynchronized(String expSQL) {
        int resultado = -1;
        try {
//			estSQL = conex.createStatement();
            estSQL.execute(expSQL);
            resultado = estSQL.getUpdateCount();
        } catch (SQLException e) {
            System.out.println("SQLException in execute method:" + e.getMessage());
        }
        return resultado;
    }

    public void openDatabase() {
        try {
            Class.forName(driver).newInstance();
        } catch (InstantiationException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        } catch (IllegalAccessException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        } catch (ClassNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        try {
            this.conex = DriverManager.getConnection(dataBase, loginDB, passwordDB);
            this.estSQL = conex.createStatement();
        } catch (SQLException e) {
            System.out.println("SQLException in openDatabase method:"
                    + e.getMessage());
        }
    }

    public void closeDatabase() {
        try {
            this.estSQL.close();
            this.conex.close();
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                preparedStatement = null;
            }
        } catch (SQLException e) {
            System.out.println("SQLException in method closeDatabase" + e.getMessage());
        }
    }

    public String getDatabaseName() {
        return databaseName;
    }

    public void setDatabaseName(String databaseName) {
        this.databaseName = databaseName;
    }

    public Connection getConnection() throws SQLException {
        try {
            Class.forName(driver);//org.gjt.mm.mysql.Driver
            this.conex = DriverManager.getConnection(dataBase, loginDB, passwordDB);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        return DriverManager.getConnection(dataBase, loginDB, passwordDB);
    }

    /**
     * ALAN GONZALEZ METODO QUE ACTUALIZA LA BASE DE DATOS
     *
     * @param expSQL
     */
    public void executeUpdate(String expSQL) {
        try {
            estSQL.execute(expSQL);
        } catch (SQLException e) {
            System.out.println("##### Query Error: #####" + expSQL);
        }
    }
}
